package com.iweb.report_statistic.dao;


import com.iweb.report_statistic.model.Report;
import com.iweb.report_statistic.model.Total;
import com.iweb.util.JdbcUtil;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TotalDaoImpl implements TotalDao{
    @Override
    public Total total() {
        String sql ="SELECT sum(quantity) as totalProducts,sum(price*quantity) as totalStockValue\n" +
                "FROM  product p \n" +
                "JOIN inventory i ON p.id = i.product_id \n" +
                "LEFT JOIN (SELECT  product_id, SUM(quantity) AS total_in  FROM  stock_in \n" +
                "GROUP BY  product_id ) AS SUM_IN ON p.id = SUM_IN.product_id \n" +
                "LEFT JOIN ( SELECT  product_id, SUM(quantity) AS total_out  FROM  stock_out \n" +
                "GROUP BY product_id ) AS SUM_OUT ON p.id = SUM_OUT.product_id ";
        List<Map<String,Object>> list = JdbcUtil.selectBySql(sql);
        Map<String,Object> map = list.get(0);
        BigDecimal totalProducts =(BigDecimal)map.get("totalProducts");
        BigDecimal totalStockValue =(BigDecimal)map.get("totalStockValue");
        Total total = new Total(totalProducts,totalStockValue);
        return total;
    }
}
